//
//  DataBase.m
//  FMDBDemo
//
//  Created by Zeno on 16/5/18.
//  Copyright © 2016年 zenoV. All rights reserved.
//

#import "KADataBase.h"

#import "KACategoryModel.h"
#import "KAAccountModel.h"
#import "KAUserinfoModel.h"
#import "KABadgeModel.h"
#import "KAChartModel.h"
#import "KAAccountGroupModel.h"

static KADataBase *_DBCtl = nil;

@interface KADataBase()<NSCopying,NSMutableCopying>{
    FMDatabase  *_db;
    
}

@end

@implementation KADataBase

+(instancetype)sharedDataBase{
    
    if (_DBCtl == nil) {
        
        _DBCtl = [[KADataBase alloc] init];
        
        [_DBCtl initDataBase];
        
    }
    
    return _DBCtl;
    
}

+(instancetype)allocWithZone:(struct _NSZone *)zone{
    
    if (_DBCtl == nil) {
        
        _DBCtl = [super allocWithZone:zone];
        
    }
    
    return _DBCtl;
    
}

-(id)copy{
    
    return self;
    
}

-(id)mutableCopy{
    
    return self;
}

-(id)copyWithZone:(NSZone *)zone{
    
    return self;
    
}

-(id)mutableCopyWithZone:(NSZone *)zone{
    
    return self;
    
}


-(void)initDataBase{
    // 获得Documents目录路径
    
    NSString *documentsPath = [NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES) lastObject];
    
    // 文件路径
    
    NSString *filePath = [documentsPath stringByAppendingPathComponent:@"keepaccount.sqlite"];
    
    // 实例化FMDataBase对象
    
    _db = [FMDatabase databaseWithPath:filePath];
    
    _db.traceExecution = YES;
    
    if ([_db open]) {
        
        // 初始化数据表
        NSString *accountSql = @"CREATE TABLE IF NOT EXISTS 'Account' ('id' INTEGER PRIMARY KEY AUTOINCREMENT  NOT NULL ,'date' DATE,'money' float,'type' INT, 'categoryid' INTEGER, 'note' VARCHAR(255))";
        NSString *categorySql = @"CREATE TABLE 'Category' ('id' INTEGER PRIMARY KEY AUTOINCREMENT  NOT NULL ,'name' VARCHAR(255),'image_normal' VARCHAR(255),'image_selected' VARCHAR(255),'image_s' VARCHAR(255),'type' INT, 'sortnum' INTEGER)";
        NSString *badgeSql = @"CREATE TABLE 'Badge' ('id' INTEGER PRIMARY KEY AUTOINCREMENT  NOT NULL ,'name' VARCHAR(255),'image_get' VARCHAR(255),'image_notget' VARCHAR(255),'type' INT,'condition' INT,'status' INT)";
        NSString *userSql = @"CREATE TABLE 'User' ('id' INTEGER PRIMARY KEY AUTOINCREMENT  NOT NULL ,'name' VARCHAR(255),'image' VARCHAR(255),'gender' INT,'phone' VARCHAR(255),'clockCount' INTEGER, 'firstaccountDate' DATE, 'lastClockDate' DATE)";
        
        [_db executeUpdate:accountSql];
        if ([_db executeUpdate:userSql]) {
            [_db executeUpdate:@"INSERT INTO User (gender) VALUES (?);", @(-1)];
        }
        
        if ([_db executeUpdate:categorySql]) {
            
            // Category 支出表写入数据
            NSArray *incomeImgArray = @[@"category_e_shopping", @"category_e_catering", @"category_e_commodity", @"category_e_traffic", @"category_e_vegetable", @"category_e_fruite", @"category_e_snack", @"category_e_sport", @"category_e_entertainmente", @"category_e_communicate", @"category_e_dress", @"category_e_beauty", @"category_e_house", @"category_e_home", @"category_e_child", @"category_e_elder", @"category_e_social", @"category_e_travel", @"category_e_smoke", @"category_e_digital", @"category_e_car", @"category_e_medical", @"category_e_books", @"category_e_study", @"category_e_pet", @"category_e_money", @"category_e_gift", @"category_e_office", @"category_e_repair", @"category_e_donate", @"category_e_lottery", @"category_e_friend", @"category_e_express"];
            NSArray *incomeNameArray = @[@"购物", @"餐饮", @"日用", @"交通", @"蔬菜", @"水果", @"零食", @"运动", @"娱乐", @"通讯", @"服饰", @"美容", @"住房", @"居家", @"孩子", @"长辈", @"社交", @"旅行", @"烟酒", @"数码", @"汽车", @"医疗", @"书籍", @"学习", @"宠物", @"礼金", @"礼物", @"办公", @"维修", @"捐赠", @"彩票", @"亲友", @"快递"];
            for (int i = 0; i < incomeImgArray.count; i++) {
                
                NSString *imageName = incomeImgArray[i];
                NSString *image_normal = [NSString stringWithFormat:@"%@_normal", imageName];
                NSString *image_selected = [NSString stringWithFormat:@"%@_selected", imageName];
                NSString *image_s = [NSString stringWithFormat:@"%@_s", imageName];
                NSString *name = incomeNameArray[i];
                
                [_db executeUpdate:@"INSERT INTO Category (name, image_normal, image_selected, image_s, type, sortnum) VALUES (?, ?, ?, ?, ?, ?);", name, image_normal, image_selected, image_s, @(0), @(i + 1)];
            }
            
            // Category 收入 表写入数据
            NSArray *expenditureImgArray = @[@"category_i_wage", @"category_i_parttimework", @"category_i_finance", @"category_i_finance", @"category_e_money", @"category_i_other"];
            NSArray *expenditureNameArray = @[@"购物", @"工资", @"兼职", @"理财", @"礼金", @"其他"];
            for (int i = 0; i < expenditureImgArray.count; i++) {
                
                NSString *imageName = expenditureImgArray[i];
                NSString *image_normal = [NSString stringWithFormat:@"%@_normal", imageName];
                NSString *image_selected = [NSString stringWithFormat:@"%@_selected", imageName];
                NSString *image_s = [NSString stringWithFormat:@"%@_s", imageName];
                NSString *name = expenditureNameArray[i];
                
                [_db executeUpdate:@"INSERT INTO Category (name, image_normal, image_selected, image_s, type, sortnum) VALUES (?, ?, ?, ?, ?, ?);", name, image_normal, image_selected, image_s, @(1), @(i + 1)];
            }
        }
        
        if ([_db executeUpdate:badgeSql]) {
            
            // 连续打卡
            NSArray *badgeImgArray0 = @[@"badge_icon_item_sign_in_continue_newer", @"badge_icon_item_sign_in_continue_3", @"badge_icon_item_sign_in_continue_7", @"badge_icon_item_sign_in_continue_21", @"badge_icon_item_sign_in_continue_50", @"badge_icon_item_sign_in_continue_100", @"badge_icon_item_sign_in_continue_200", @"badge_icon_item_sign_in_continue_365", @"badge_icon_item_sign_in_continue_500"];
            NSArray *badgeNameArray0 = @[@"新手入门", @"连续3天徽章", @"连续7天徽章", @"连续21天徽章", @"连续50天徽章", @"连续100天徽章", @"连续200天徽章", @"连续365天徽章", @"连续500天徽章"];
            NSArray *badgeConditionArray0 = @[@(1), @(3), @(7), @(21), @(50), @(100), @(200), @(356), @(500)];
            for (int i = 0; i < badgeImgArray0.count; i++) {
                
                NSString *image_get = badgeImgArray0[i];
                NSString *image_notget = [NSString stringWithFormat:@"%@_not_get", image_get];
                NSString *name = badgeNameArray0[i];
                
                [_db executeUpdate:@"INSERT INTO Badge (name, image_get, image_notget, type, condition, status) VALUES (?, ?, ?, ?, ?, ?);", name, image_get, image_notget, @(1), badgeConditionArray0[i], @(0)];
            }
            
            // 累计记账天数
            NSArray *badgeImgArray1 = @[@"badge_icon_item_cumulative_day_30", @"badge_icon_item_cumulative_day_100", @"badge_icon_item_cumulative_day_365", @"badge_icon_item_cumulative_day_500", @"badge_icon_item_cumulative_day_800", @"badge_icon_item_cumulative_day_1000"];
            NSArray *badgeNameArray1 = @[@"累计记账30天", @"累计记账100天", @"累计记账365天", @"累计记账500天", @"累计记账800天", @"累计记账1000天"];
            NSArray *badgeConditionArray1 = @[@(30), @(100), @(356), @(500), @(800), @(1000)];
            for (int i = 0; i < badgeImgArray1.count; i++) {
                
                NSString *image_get = badgeImgArray1[i];
                NSString *image_notget = [NSString stringWithFormat:@"%@_not_get", image_get];
                NSString *name = badgeNameArray1[i];
                
                [_db executeUpdate:@"INSERT INTO Badge (name, image_get, image_notget, type, condition, status) VALUES (?, ?, ?, ?, ?, ?);", name, image_get, image_notget, @(2), badgeConditionArray1[i], @(0)];
            }
            
            // 累计记账笔数
            NSArray *badgeImgArray2 = @[@"badge_icon_item_cumulative_count_99", @"badge_icon_item_cumulative_count_333", @"badge_icon_item_cumulative_count_555", @"badge_icon_item_cumulative_count_888", @"badge_icon_item_cumulative_count_1024", @"badge_icon_item_cumulative_count_2046"];
            NSArray *badgeNameArray2 = @[@"累计记账99笔", @"计记账333笔", @"累计记账555笔", @"累计记账888笔", @"累计记账1024笔", @"累计记账2046笔"];
            NSArray *badgeConditionArray2 = @[@(99), @(333), @(555), @(888), @(1024), @(2046)];
            for (int i = 0; i < badgeImgArray2.count; i++) {
                
                NSString *image_get = badgeImgArray2[i];
                NSString *image_notget = [NSString stringWithFormat:@"%@_not_get", image_get];
                NSString *name = badgeNameArray2[i];
                
                [_db executeUpdate:@"INSERT INTO Badge (name, image_get, image_notget, type, condition, status) VALUES (?, ?, ?, ?, ?, ?);", name, image_get, image_notget, @(3), badgeConditionArray2[i], @(0)];
            }
        }
    }
    
    [_db close];

}
#pragma mark - Account
- (void)addAccountModel:(KAAccountModel *)accountModel
{
    
    if ([_db open]) {
        
        [_db executeUpdate:@"INSERT INTO Account(date, money, type, categoryid, note) VALUES(?, ?, ?, ?, ?)", accountModel.date, @(accountModel.money), @(accountModel.type), @(accountModel.categoryid), accountModel.note];
    }
    
    [_db close];
}

/**  删除accountModel */
- (void)deleteAccount:(KAAccountModel *)accountModel
{
    if ([_db open]) {
        
        [_db executeUpdate:@"DELETE FROM Account WHERE id = ?", @(accountModel.id)];
    }

    [_db close];
}

- (void)updateAccount:(KAAccountModel *)accountModel
{
    if ([_db open]) {
        
        if (accountModel.date.length > 0) {
            [_db executeUpdate:@"UPDATE 'Account' SET date = ? where id = ?", accountModel.date, @(accountModel.id)];
        }
        if (accountModel.money > 0) {
            [_db executeUpdate:@"UPDATE 'Account' SET money = ? where id = ?", @(accountModel.money), @(accountModel.id)];
        }
        if (accountModel.type > -1) {
            [_db executeUpdate:@"UPDATE 'Account' SET type = ? where id = ?", @(accountModel.type), @(accountModel.id)];
        }
        if (accountModel.categoryid > 0) {
            [_db executeUpdate:@"UPDATE 'Account' SET categoryid = ? where id = ?", @(accountModel.categoryid), @(accountModel.id)];
        }
        if (accountModel.note.length > 0) {
            [_db executeUpdate:@"UPDATE 'Account' SET note = ? where id = ?", accountModel.note, @(accountModel.id)];
        }
    }
    
    [_db close];
}



- (NSMutableArray<KAAccountModel *> *)getAccountModelWithYear:(NSString *)year month:(NSString *)month
{
    
    NSMutableArray *dataArray = [[NSMutableArray alloc] init];
    if ([_db open]) {
        
        FMResultSet *res = nil;
        if (year && month) {
            
            res = [_db executeQuery:@"select a.id, a.money, a.date, a.money, a.type as 'a.type', a.categoryid, a.note, name, image_normal, image_selected, image_s, c.type as 'c.type', sortnum from Account a, Category c where a.categoryid = c.id and strftime('%Y', date) = ? and CAST(strftime('%m', date) as int) = ? order by date desc, a.id desc", year, month];
        }else if(year){
            res = [_db executeQuery:@"select * from Account where strftime('%Y', date) == '?' order by id desc", year];
        }
        while ([res next]) {
            
            KAAccountModel *accountModel = [[KAAccountModel alloc] init];
            accountModel.id = [res intForColumn:@"id"];
            accountModel.date = [res stringForColumn:@"date"];
            accountModel.money = [res doubleForColumn:@"money"];
            accountModel.type = [res intForColumn:@"a.type"];
            accountModel.categoryid = [res longForColumn:@"categoryid"];
            accountModel.note = [res stringForColumn:@"note"];
            [dataArray addObject:accountModel];
            
            KACategoryModel *categoryModel = [[KACategoryModel alloc] init];
            categoryModel.id = [res longForColumn:@"categoryid"];
            categoryModel.name = [res stringForColumn:@"name"];
            categoryModel.image_normal = [res stringForColumn:@"image_normal"];
            categoryModel.image_selected = [res stringForColumn:@"image_selected"];
            categoryModel.image_s = [res stringForColumn:@"image_s"];
            categoryModel.type = [res longForColumn:@"c.type"];
            categoryModel.sortnum = [res longForColumn:@"sortnum"];
            accountModel.categoryModel = categoryModel;
        }
        
    }
    [_db close];
    
    return dataArray;
}


#pragma mark - Category

- (NSMutableArray<KACategoryModel *> *)getAllCategoryModel
{
    [_db open];
    
    NSMutableArray *dataArray = [[NSMutableArray alloc] init];
    
    FMResultSet *res = [_db executeQuery:@"SELECT * FROM Category"];
    
    while ([res next]) {
        
        KACategoryModel *categoryModel = [[KACategoryModel alloc] init];
        categoryModel.id = [res longForColumn:@"id"];
        categoryModel.name = [res stringForColumn:@"name"];
        categoryModel.image_normal = [res stringForColumn:@"image_normal"];
        categoryModel.image_selected = [res stringForColumn:@"image_selected"];
        categoryModel.image_s = [res stringForColumn:@"image_s"];
        categoryModel.type = [res longForColumn:@"type"];
        categoryModel.sortnum = [res longForColumn:@"sortnum"];
        
        [dataArray addObject:categoryModel];
    }
    
    [_db close];
    
    return dataArray;
}


- (NSMutableArray<KACategoryModel *> *)getCategoryModelWithType:(NSInteger)type
{
    [_db open];
    
    NSMutableArray *dataArray = [[NSMutableArray alloc] init];
    
    FMResultSet *res = [_db executeQuery:@"SELECT * FROM Category where type=?", @(type)];
    
    while ([res next]) {
        
        KACategoryModel *categoryModel = [[KACategoryModel alloc] init];
        categoryModel.id = [res longForColumn:@"id"];
        categoryModel.name = [res stringForColumn:@"name"];
        categoryModel.image_normal = [res stringForColumn:@"image_normal"];
        categoryModel.image_selected = [res stringForColumn:@"image_selected"];
        categoryModel.image_s = [res stringForColumn:@"image_s"];
        categoryModel.type = [res longForColumn:@"type"];
        categoryModel.sortnum = [res longForColumn:@"sortnum"];
        
        [dataArray addObject:categoryModel];
    }
    
    [_db close];
    
    return dataArray;
}


#pragma mark - UserInfo
/** 更新person */
- (void)updateUserinfo:(KAUserinfoModel *)userinfoModel
{
    if ([_db open]) {
        
        if (userinfoModel.name.length > 0) {
            [_db executeUpdate:@"UPDATE 'User' SET name = ?", userinfoModel.name];
        }
        if (userinfoModel.image.length > 0) {
            [_db executeUpdate:@"UPDATE 'User' SET image = ?", userinfoModel.image];
        }
        if (userinfoModel.gender > 0) {
            [_db executeUpdate:@"UPDATE 'User' SET gender = ?", @(userinfoModel.gender)];
        }
        if (userinfoModel.phone.length > 0) {
            [_db executeUpdate:@"UPDATE 'User' SET phone = ?", userinfoModel.phone];
        }
        if (userinfoModel.clockCount > 0) {
            [_db executeUpdate:@"UPDATE 'User' SET clockCount = ?", @(userinfoModel.clockCount)];
        }
        if (userinfoModel.firstaccountDate.length > 0) {
            [_db executeUpdate:@"UPDATE 'User' SET firstaccountDate = ?", userinfoModel.firstaccountDate];
        }
        if (userinfoModel.lastClockDate.length > 0) {
            [_db executeUpdate:@"UPDATE 'User' SET lastClockDate = ?", userinfoModel.lastClockDate];
        }
    }

    [_db close];
}


/**
 *  获取person的所有车辆
 *
 */
- (KAUserinfoModel *)getUserinfo
{
    
    if ([_db open]) {
        
        FMResultSet *res = [_db executeQuery:[NSString stringWithFormat:@"SELECT * FROM User"]];
        
        KAUserinfoModel *userInfoModel = [[KAUserinfoModel alloc] init];
        if ([res next]){
            
            userInfoModel.id = [res longForColumn:@"id"];
            userInfoModel.name =  [res stringForColumn:@"name"];
            userInfoModel.image =  [res stringForColumn:@"image"];
            userInfoModel.gender =  [res intForColumn:@"gender"];
            userInfoModel.phone =  [res stringForColumn:@"phone"];
            userInfoModel.clockCount = [res longForColumn:@"clockCount"];
            userInfoModel.firstaccountDate =  [res stringForColumn:@"firstaccountDate"];
            userInfoModel.lastClockDate =  [res stringForColumn:@"lastClockDate"];
        }
        
        FMResultSet *res2 = [_db executeQuery:[NSString stringWithFormat:@"select min(date) as mindate, count(id) as count from Account"]];
        if ([res2 next]){
            
            userInfoModel.accountCount = [res2 longForColumn:@"count"];
            userInfoModel.firstaccountDate =  [res2 stringForColumn:@"mindate"];
        }
        
        return userInfoModel;
    }
    
    return nil;
}


#pragma mark - badge
/**
 *  获取所有徽章
 *
 */
- (NSMutableArray<KABadgeModel *> *)getAllBadgeModel
{
    
    return [self getBadgeModelsWithType:-1];
}
/**
 *  根据type获取徽章
 *
 */
- (NSMutableArray<KABadgeModel *> *)getBadgeModelsWithType:(NSInteger)type
{
    
    NSMutableArray *dataArray = [[NSMutableArray alloc] init];
    if ([_db open]) {
        
        FMResultSet *res = nil;
        if (type > 0) {
            res = [_db executeQuery:@"SELECT * FROM Badge where type=?", @(type)];
        }else{
            res = [_db executeQuery:@"SELECT * FROM Badge"];
        }
        
        while ([res next]) {
            
            KABadgeModel *badgeModel = [[KABadgeModel alloc] init];
            badgeModel.id = [res longForColumn:@"id"];
            badgeModel.name = [res stringForColumn:@"name"];
            badgeModel.image_get = [res stringForColumn:@"image_get"];
            badgeModel.image_notget = [res stringForColumn:@"image_notget"];
            badgeModel.type = [res intForColumn:@"type"];
            badgeModel.condition = [res intForColumn:@"condition"];
            badgeModel.status = [res intForColumn:@"status"];
            
            [dataArray addObject:badgeModel];
        }
    }
    
    [_db close];
    
    return dataArray;
}


/**
 *  更新徽章
 *
 */
- (void)updateBadgeWithType:(NSInteger)type
{
    if ([_db open]) {
        
        if (type == 1) {
            
            FMResultSet *res = [_db executeQuery:[NSString stringWithFormat:@"SELECT * FROM User"]];
            NSInteger clockCount = 0;
            if ([res next]){
                
                clockCount = [res longForColumn:@"clockCount"];
            }
            [_db executeUpdate:@"UPDATE 'Badge' SET status = 1 where type = ? and condition <= ?", @(type), @(clockCount)];
        }else if (type == 2){
            
            FMResultSet *res = [_db executeQuery:[NSString stringWithFormat:@"select min(date) as mindate from Account"]];
            NSInteger dayCount = 0;
            if ([res next]){
                
                NSString *firstaccountDateStr =  [res stringForColumn:@"mindate"];
                NSDateFormatter *formatter = [[NSDateFormatter alloc] init];
                formatter.dateFormat = @"yyyy-MM-dd";
                NSDate *firstAccountDate = [formatter dateFromString:firstaccountDateStr];
                NSInteger secondDif = [firstAccountDate timeIntervalSinceNow];
                
                dayCount = ABS(floorf(secondDif * 1.0 / (24 * 60 * 60)));
            }
            [_db executeUpdate:@"UPDATE 'Badge' SET status = 1 where type = ? and condition <= ?", @(type), @(dayCount)];
        }else if (type == 3){
            
            NSInteger accountCount = 0;
            FMResultSet *res2 = [_db executeQuery:[NSString stringWithFormat:@"select count(id) as count from Account"]];
            if ([res2 next]){
                
                accountCount = [res2 longForColumn:@"count"];
            }
            [_db executeUpdate:@"UPDATE 'Badge' SET status = 1 where type = ? and condition <= ?", @(type), @(accountCount)];
        }
    }
}


#pragma mark - KAChartModel
- (NSMutableArray<KAChartModel *> *)getModelArrayWithDataType:(DataType)dataType type:(NSInteger)type
{
    NSMutableArray *dataArray = [[NSMutableArray alloc] init];
    if ([_db open]) {
        
        FMResultSet *res = nil;
        if (dataType == DataTypeThisWeek) {
            
            res = [_db executeQuery:@"SELECT date, sum(money) as sum FROM Account where date>=date(datetime('now','start of day','-7 day','weekday 1'))  and date<date(datetime('now','start of day','+0 day','weekday 1')) and type = ? group by date", @(type)];
        }else if (dataType == DataTypeThisMonth){
            
            res = [_db executeQuery:@"SELECT date, sum(money) as sum FROM Account where date>=date(datetime('now','start of month','-1 second')) AND date<date(datetime('now','start of month','+1 month','0 day')) and type = ? group by date", @(type)];
        }else if (dataType == DataTypeThisYear){
            
            res = [_db executeQuery:@"SELECT strftime('%Y-%m', date) as date, sum(money) as sum FROM Account where date>=date(datetime('now','start of year','-0 day')) AND date<date(datetime('now','start of year','+12 month')) and type = ? group by strftime('%m', date)", @(type)];
        }
        
        while ([res next]) {
            
            KAChartModel *chartModel = [[KAChartModel alloc] init];
            chartModel.money = [res doubleForColumn:@"sum"];
            chartModel.time = [res stringForColumn:@"date"];
            
            [dataArray addObject:chartModel];
        }
    }
    
    [_db close];
    
    return dataArray;
}


- (NSMutableArray<KAAccountGroupModel *> *)getGroupModelArrayWithDataType:(DataType)dataType type:(NSInteger)type
{
    NSMutableArray *dataArray = [[NSMutableArray alloc] init];
    if ([_db open]) {
        
        FMResultSet *res = nil;
        FMResultSet *totalMoneyRes = nil;
        if (dataType == DataTypeThisWeek) {
            
            totalMoneyRes = [_db executeQuery:@"SELECT sum(money) as sum FROM Account where date>=date(datetime('now','start of day','-7 day','weekday 1'))  and date<date(datetime('now','start of day','+0 day','weekday 1')) and type = ?", @(type)];
            res = [_db executeQuery:@"SELECT categoryid, sum(money) as sum, name, image_s, a.type as type FROM Account a, Category c where categoryid in(SELECT categoryid FROM Account where date>=date(datetime('now','start of day','-7 day','weekday 1'))  and date<date(datetime('now','start of day','+0 day','weekday 1')) and type = ?) and a.categoryid = c.id group by categoryid order by sum(money) desc", @(type)];
        }else if (dataType == DataTypeThisMonth){
            
            totalMoneyRes = [_db executeQuery:@"SELECT sum(money) as sum FROM Account where date>=date(datetime('now','start of month','-1 second')) AND date<date(datetime('now','start of month','+1 month','0 day')) and type = ?", @(type)];
            res = [_db executeQuery:@"SELECT categoryid, sum(money) as sum, name, image_s, a.type as type  FROM Account a, Category c where categoryid in(SELECT categoryid FROM Account where date>=date(datetime('now','start of month','-1 second')) AND date<date(datetime('now','start of month','+1 month','0 day')) and type = ?) and a.categoryid = c.id group by categoryid order by sum(money) desc", @(type)];
        }else if (dataType == DataTypeThisYear){
            
            totalMoneyRes = [_db executeQuery:@"SELECT sum(money) as sum FROM Account where date>=date(datetime('now','start of year','-0 day')) AND date<date(datetime('now','start of year','+12 month')) and type = ?", @(type)];
            res = [_db executeQuery:@"SELECT categoryid, sum(money) as sum, name, image_s, a.type as type  FROM Account a, Category c where categoryid in(SELECT categoryid FROM Account where date>=date(datetime('now','start of year','-0 day')) AND date<date(datetime('now','start of year','+12 month')) and type = ?) and a.categoryid = c.id group by categoryid order by sum(money) desc", @(type)];
        }
        
        double totalMoney = 0;
        while ([totalMoneyRes next]) {
            
            totalMoney = [totalMoneyRes doubleForColumn:@"sum"];
        }
        
        while ([res next]) {
            
            KAAccountGroupModel *groupModel = [[KAAccountGroupModel alloc] init];
            groupModel.money = [res doubleForColumn:@"sum"];
            groupModel.totalMoney = totalMoney;
            groupModel.categoryid = [res longForColumn:@"categoryid"];
            groupModel.categoryName = [res stringForColumn:@"name"];
            groupModel.categoryImage = [res stringForColumn:@"image_s"];
            groupModel.categoryid = [res intForColumn:@"type"];
            
            [dataArray addObject:groupModel];
        }
    }
    
    [_db close];
    
    return dataArray;
}


@end
